import com.shiyi.compute;

import java.sql.*;
import java.util.LinkedHashMap;
import java.util.Map;

public class test3 {
    public static final String url = "jdbc:mysql://rm-uf6v59d4fuzneh9mpko.mysql.rds.aliyuncs.com:3306/gbtest";
    public static final String user = "shiyidata";
    public static final String passWord = "Bestage1116";
    public static final String table = "xf_package";
    //    public static final String table = "xf_test";
//    public static final String table = "test";
    public static final String endtable = "xf_pack_end10";
    //    public static final String endtable = "xf_package1";
    public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
        //加载mysql驱动
//        Class.forName("com.mysql.jdbc.Driver");
        //获取数据库连接
        Connection connection = DriverManager.getConnection(url, user, passWord);
        connection.setAutoCommit(false);
//        PreparedStatement preparedStatement = connection.prepareStatement("select  * from order_test")

        /*
         * 前置操作
         *   1.顺丰表清除
         *   2.顺丰表过滤字段并插入
         *   3.包裹表过滤和插入数据
         *   4.更新包裹表不可见字符
         *   xf_package清除
         *   5.xf_package中插入数据
         *   6.将包裹标准重量过大的带有都好的更改
         *   7.清理最终表数据
         * */

        //1 顺丰表清除
        PreparedStatement truncate_table_xf_foundation1 = connection.prepareStatement("truncate table xf_foundation1");
        truncate_table_xf_foundation1.execute();
        truncate_table_xf_foundation1.close();

        //2.顺丰表过滤字段并插入
        PreparedStatement pxf = connection.prepareStatement("insert into xf_foundation1 select 序号,日期,运单号码,寄件地区,到件地区,对方公司名称,实际重量,计费重量,产品类型," +
                "       付款方式,`费用(元)`,`折扣/促销`,应付金额,经手人,增值费用,`始发地(省名)`,`目的地(省名)`" +
                "from xf_foundation");
        pxf.execute();
        pxf.close();

        //清除package_simple
        PreparedStatement truncate_table_package_simple = connection.prepareStatement("truncate table package_simple");
        truncate_table_package_simple.execute();
        truncate_table_package_simple.close();

        //3.包裹表过滤和插入数据
        PreparedStatement ppack = connection.prepareStatement("insert into package_simple select 物流单号,物流公司,称重重量,标准重量 " +
                "from package_details_test");
        ppack.execute();
        ppack.close();

        //4.更新包裹表不可见字符
        PreparedStatement pupset = connection.prepareStatement("update package_simple set 物流单号 = replace(物流单号,'\t','')");
        pupset.execute();
        pupset.close();

        //xf_package清除
        PreparedStatement truncate_table_xf_package = connection.prepareStatement("truncate table xf_package");
        truncate_table_xf_package.execute();
        truncate_table_xf_package.close();

        //5.xf_package中插入数据
        PreparedStatement pxfinsert = connection.prepareStatement("insert into xf_package select * from xf_foundation1" +
                " join package_simple " +
                " where 运单号码 = 物流单号");
        pxfinsert.execute();
        pxfinsert.close();
        System.out.println("--------------------------------------");
        //6.将包裹标准重量过大的带有都好的更改
        PreparedStatement pupxfset = connection.prepareStatement("update xf_package set 包裹标准重量 = replace(包裹标准重量,',','')");
        pupxfset.execute();
        pupxfset.close();

        //System.out.println(preparedStatement.getFetchSize());
        //获取日期 次数
        Map<String, Integer> map = new LinkedHashMap<String, Integer>();
        PreparedStatement pcount = connection.prepareStatement("select  substr(日期,1,2) 日期,count(*) 次数 from " +table + " group by substr(日期,1,2) ");
        ResultSet count = pcount.executeQuery();
        while (count.next()){
            String data = count.getString("日期");
            int counts =Integer.parseInt(count.getString("次数")) /30;
            System.out.println(Integer.parseInt(count.getString("次数")));
            map.put(data,counts);
        }
        pcount.close();
        count.close();

//            7.清理最终表数据
        PreparedStatement truncate = connection.prepareStatement("truncate table " + endtable );
        truncate.execute();
        truncate.close();

        /**
         * 创建插入表
         */
        PreparedStatement create = connection.prepareStatement("create table if not exists " + endtable+"(" +
                "  `序号` varchar(255) ," +
                "  `日期` varchar(255) ," +
                "  `运单号码` varchar(255) ," +
                "  `寄件地区` varchar(255)," +
                "  `到件地区` varchar(255) ," +
//                    "  `对方公司名称` varchar(255) ," +
//                    "  `实际重量` varchar(255) ," +     //不要
                "  `计费重量` varchar(255) ," +
                "  `产品类型` varchar(255) ," +
//                    "  `付款方式` varchar(255) ," +     //不要
                "  `费用(元)` varchar(255) ," +
                "  `折扣/促销` varchar(255) ," +
                "  `应付金额` varchar(255)," +
//                    "  `经手人` varchar(255)," +       //不要
                "  `增值费用` varchar(255)," +
                "  `始发地(省名)` varchar(255) ," +
                "  `目的地(省名)` varchar(255) ," +
//                    "  `包裹物流单号` varchar(255)," +
//                    "  `包裹物流公司` varchar(255)," +
                "  `包裹称重重量` varchar(255)," +
                "  `包裹标准重量` varchar(255)," +
                "  `核对计费重量` varchar(255)," +
                "  `重量差` DECIMAL(9,3)," +
                "    `计算运费` DECIMAL(9,3)," +
                "    `差异金额` DECIMAL(9,3)," +
                "  `备注` varchar(255)"+
                ") ");
        create.execute();
        create.close();

        PreparedStatement pinsert = null;
        pinsert = connection.prepareStatement("insert into "+endtable+" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        int i = 0;
        //便利日期
        for(String key:map.keySet()) {
//          System.out.println(key+ ":" + map.get(key));

            //获得以天为单位的数据
            PreparedStatement pdata = connection.prepareStatement("select * from " + table+" where substr(日期,1,2) = '" + key + "'");
//                PreparedStatement pdata = connection.prepareStatement("select * from " + table+" where 日期 = '09-01' ");
            ResultSet rdata = pdata.executeQuery();
//                System.out.println(key);
            //遍历每天的数据
            while (rdata.next()){

                //获取每行数据
                //序号hao  始发地(省名)origin_address  目的地(省名) destination 包裹称重重量weight  包裹标准重量standard_weight 计费重量bill_weight
                //删除表

                /**
                 * 计算逻辑
                 */
                String hao = rdata.getString("序号");
                String data = rdata.getString("日期");
                String freight = rdata.getString("运单号码");
                String adress = rdata.getString("寄件地区");
                String endadress = rdata.getString("到件地区");
                String company = rdata.getString("对方公司名称");
                String weight_true= rdata.getString("实际重量");
                String bill_weight = rdata.getString("计费重量");
                String product_type = rdata.getString("产品类型");
                String payment_method = rdata.getString("付款方式");
                String cost = rdata.getString("费用(元)");
                String reduce = rdata.getString("折扣/促销");
                String cost_true = rdata.getString("应付金额");
                String person = rdata.getString("经手人");
                String cost_add = rdata.getString("增值费用");
                String origin_address = rdata.getString("始发地(省名)");
                String destination = rdata.getString("目的地(省名)");
                String order_num = rdata.getString("包裹物流单号");
                String company_logistics = rdata.getString("包裹物流公司");
                Integer num = map.get(key);//次数
                String weight = rdata.getString("包裹称重重量");
                String standard_weight = rdata.getString("包裹标准重量");
//                    System.out.println(hao+ "     ");
                if (null==cost_add )
                {
//                        System.out.println(cost_add);
                    String ss = "";
                    double cost_weight = compute.compute_weight(hao, weight, standard_weight, bill_weight);
                    double v = compute.compute_price( hao ,product_type,origin_address,endadress, destination, cost_true,num, cost_weight);
                    double vv=Double.parseDouble(cost_true)-v;
                    if (vv <0){
                        vv = 0.00;
                    }
                    pinsert.setString(1,hao);
                    pinsert.setString(2,data);
                    pinsert.setString(3,freight);
                    pinsert.setString(4,adress);
                    pinsert.setString(5,endadress);
//                        pinsert.setString(6,company);
//                        pinsert.setString(7,weight_true);
                    pinsert.setString(6,bill_weight);
                    pinsert.setString(7,product_type);
//                        pinsert.setString(10,payment_method);
                    pinsert.setString(8,cost);
                    pinsert.setString(9,reduce);
                    pinsert.setString(10,cost_true);
//                        pinsert.setString(14,person);
                    pinsert.setString(11,cost_add);
                    pinsert.setString(12,origin_address);
                    pinsert.setString(13,destination);
//                        pinsert.setString(18,order_num);
//                        pinsert.setString(19,company_logistics);
                    pinsert.setString(14,weight);
                    pinsert.setString(15,standard_weight);
                    pinsert.setDouble(16,cost_weight);
                    String weight_diff = Math.abs(cost_weight - Double.parseDouble(bill_weight))+"";
                    pinsert.setString(17,weight_diff);
                    pinsert.setDouble(18,v);
                    pinsert.setDouble(19,vv);
                    if (Double.parseDouble(weight_diff) > 0 ) ss+="重量有差异 ";
                    if ("顺丰标快".equals(product_type) &&  (!"西藏".equals(destination)) && !"玉树藏族".equals(endadress)) ss +="为什么是顺丰标快 ";
                    if ("顺丰特快".equals(product_type) && !"西藏".equals(destination)) ss +="为什么是顺丰特快 ";
                    pinsert.setString(20,ss);
                    pinsert.addBatch();
                    i++;
                }
                else if ( "同城退回".equals(cost_add)){
                    String ss = "";
                    double cost_weight = compute.compute_weight(hao, weight, standard_weight, bill_weight);
                    double v = compute.compute_price( hao ,product_type,origin_address,endadress, destination, cost_true,2500, cost_weight);
                    double vv=Double.parseDouble(cost_true)-v;
                    if (vv <0){
                        vv = 0.00;
                    }
                    pinsert.setString(1,hao);
                    pinsert.setString(2,data);
                    pinsert.setString(3,freight);
                    pinsert.setString(4,adress);
                    pinsert.setString(5,endadress);
//                        pinsert.setString(6,company);
//                        pinsert.setString(7,weight_true);
                    pinsert.setString(6,bill_weight);
                    pinsert.setString(7,product_type);
//                        pinsert.setString(10,payment_method);
                    pinsert.setString(8,cost);
                    pinsert.setString(9,reduce);
                    pinsert.setString(10,cost_true);
//                        pinsert.setString(14,person);
                    pinsert.setString(11,cost_add);
                    pinsert.setString(12,origin_address);
                    pinsert.setString(13,destination);
//                        pinsert.setString(18,order_num);
//                        pinsert.setString(19,company_logistics);
                    pinsert.setString(14,weight);
                    pinsert.setString(15,standard_weight);
                    pinsert.setDouble(16,cost_weight);
                    String weight_diff = Math.abs(cost_weight - Double.parseDouble(bill_weight))+"";
                    pinsert.setString(17,weight_diff);
                    pinsert.setDouble(18,v);
                    pinsert.setDouble(19,vv);
                    if (Double.parseDouble(weight_diff) > 0 ) ss+="重量有差异 ";
                    if ("顺丰标快".equals(product_type) &&  (!"西藏".equals(destination)) && !"玉树藏族".equals(endadress)) ss +="为什么是顺丰标快 ";
                    if ("顺丰特快".equals(product_type) && !"西藏".equals(destination)) ss +="为什么是顺丰特快 ";
                    pinsert.setString(20,ss);
                    pinsert.addBatch();
                    i++;
                }else if ("异地退回".equals(cost_add)){
                    String ss = "";
                    double cost_weight = compute.compute_weight(hao, weight, standard_weight, bill_weight);
                    double v = compute.compute_price( hao ,product_type,destination,endadress,origin_address , cost,2500, cost_weight);
                    double vv=Double.parseDouble(cost)-v;
                    if (vv <0){
                        vv = 0.00;
                    }
                    pinsert.setString(1,hao);
                    pinsert.setString(2,data);
                    pinsert.setString(3,freight);
                    pinsert.setString(4,adress);
                    pinsert.setString(5,endadress);
//                        pinsert.setString(6,company);
//                        pinsert.setString(7,weight_true);
                    pinsert.setString(6,bill_weight);
                    pinsert.setString(7,product_type);
//                        pinsert.setString(10,payment_method);
                    pinsert.setString(8,cost);
                    pinsert.setString(9,reduce);
                    pinsert.setString(10,cost_true);
//                        pinsert.setString(14,person);
                    pinsert.setString(11,cost_add);
                    pinsert.setString(12,origin_address);
                    pinsert.setString(13,destination);
//                        pinsert.setString(18,order_num);
//                        pinsert.setString(19,company_logistics);
                    pinsert.setString(14,weight);
                    pinsert.setString(15,standard_weight);
                    pinsert.setDouble(16,cost_weight);
                    String weight_diff = Math.abs(cost_weight - Double.parseDouble(bill_weight))+"";
                    pinsert.setString(17,weight_diff);
                    pinsert.setDouble(18,v);
                    pinsert.setDouble(19,vv);
                    if (Double.parseDouble(weight_diff) > 0 ) ss+="重量有差异 ";
                    if ("顺丰标快".equals(product_type) && !"西藏".equals(origin_address)) ss +="为什么是顺丰标快 ";
                    if ("顺丰特快".equals(product_type) && !"西藏".equals(origin_address)) ss +="为什么是顺丰标快 ";
                    pinsert.setString(20,ss);
                    pinsert.addBatch();
                    i++;
                }
                else {
                    pinsert.setString(1,hao);
                    pinsert.setString(2,data);
                    pinsert.setString(3,freight);
                    pinsert.setString(4,adress);
                    pinsert.setString(5,endadress);
//                        pinsert.setString(6,company);
//                        pinsert.setString(7,weight_true);
                    pinsert.setString(6,bill_weight);
                    pinsert.setString(7,product_type);
//                        pinsert.setString(10,payment_method);
                    pinsert.setString(8,cost);
                    pinsert.setString(9,reduce);
                    pinsert.setString(10,cost_true);
//                        pinsert.setString(14,person);
                    pinsert.setString(11,cost_add);
                    pinsert.setString(12,origin_address);
                    pinsert.setString(13,destination);
//                        pinsert.setString(18,order_num);
//                        pinsert.setString(19,company_logistics);
                    pinsert.setString(14,weight);
                    pinsert.setString(15,standard_weight);
                    pinsert.setDouble(16,Double.parseDouble(bill_weight));
                    pinsert.setString(17,"0");
                    pinsert.setDouble(18,Double.parseDouble(cost_true) );
                    pinsert.setDouble(19,0.00);
                    pinsert.setString(20," ");
                    pinsert.addBatch();
                    i++;

                }

                while (i>3000)
                {
                    pinsert.executeBatch();
                    i = 0;
                    connection.commit();
                }

                /**
                 * 插入数据
                 */

//                    System.out.println( hao+" " + key + " " + origin_address+" "+" "+destination + " "+
//                            num + " " + weight + " " + standard_weight + " " + bill_weight +" " +
//                            compute.compute_price(origin_address, destination, num, compute.compute_weight(weight, standard_weight, bill_weight)));

//                    System.out.println("-----------------------------------------");
            }

            pinsert.executeBatch();
            connection.commit();
        }
        pinsert.close();
        PreparedStatement pup = connection.prepareStatement("select 序号 from " + endtable +" where 运单号码 in "+ " (select distinct(运单号码) from " + endtable + " group by `运单号码` having count(*) > 1) "+" and 寄件地区 <>'上海' and 到件地区 = '上海' and 增值费用 is null");
        ResultSet resultSets = pup.executeQuery();
        while (resultSets.next())
        {
            String xuhao = resultSets.getString("序号");
            PreparedStatement pset = connection.prepareStatement("update " + endtable + " set `备注` = '`备注`+'应该是退回件' where 序号 = " + xuhao);
            pset.executeUpdate();
            pset.close();
        }

    }
}
